Is Col 1 Data In Col 2 and vice versa
In Excel, you can compare the cells in two ranges with an array formula.
Consider this table - look at columns 1 and 4 - note the occurrence of the values
| Value |
Is A in D |
(BlankCell) |
Value |
Is D in A |
| 1 |
Yes |
|
0 |
No |
| 2 |
Yes |
|
1 |
Yes |
| 3 |
No |
|
2 |
Yes |
| 4 |
No |
|
6 |
No |
| 5 |
No |
|
7 |
No |
The table is obtained by :
| Value |
Is A in D |
(BlankCell) |
Value |
Is D in A |
| 1 |
| =IF(ISNA(VLOOKUP(A2,$D$2:$D$6,1,FALSE)),"No","Yes") |
|
|
0 |
| =IF(ISNA(VLOOKUP(D2,$A$2:$A$6,1,FALSE)),"No","Yes") |
|
| 2 |
| =IF(ISNA(VLOOKUP(A3,$D$2:$D$6,1,FALSE)),"No","Yes") |
|
|
1 |
| =IF(ISNA(VLOOKUP(D3,$A$2:$A$6,1,FALSE)),"No","Yes") |
|
| 3 |
| =IF(ISNA(VLOOKUP(A4,$D$2:$D$6,1,FALSE)),"No","Yes") |
|
|
2 |
| =IF(ISNA(VLOOKUP(D4,$A$2:$A$6,1,FALSE)),"No","Yes") |
|
| 4 |
| =IF(ISNA(VLOOKUP(A5,$D$2:$D$6,1,FALSE)),"No","Yes") |
|
|
6 |
| =IF(ISNA(VLOOKUP(D5,$A$2:$A$6,1,FALSE)),"No","Yes") |
|
| 5 |
| =IF(ISNA(VLOOKUP(A6,$D$2:$D$6,1,FALSE)),"No","Yes") |
|
|
7 |
| =IF(ISNA(VLOOKUP(D6,$A$2:$A$6,1,FALSE)),"No","Yes") |
|
|